#include "usqlite.h"

#define DBNAME "user.db"
#define N 1024*3

sqlite3 *db = NULL;

// 初始化数据库
int sq_init()
{
    // 打开数据库
    if (SQLITE_OK != sqlite3_open(DBNAME, &db)){
        printf("数据库打开失败\n");
        return -1;
    }

    // 创建表
    char sq_buff[N] = "create table if not exists user(ID char primary key,\
                                                        password char,\
                                                        deviceonline int,\
                                                        swlight int,\
                                                        swfan int,\
                                                        swmotor int,\
                                                        tempup int,\
                                                        tempdown int,\
                                                        humeup int,\
                                                        humedown int,\
                                                        luxup int,\
                                                        luxdown int)";
    if (SQLITE_OK != sqlite3_exec(db, sq_buff, NULL, NULL, NULL)){
        printf("建表失败\n");
        return -1;
    }

    //添加一条信息
    msg_t msg;
    memset(&msg, 0, sizeof(msg_t));
    strcpy(msg.user.ID, "zhangsan");
    strcpy(msg.user.password, "123456");
    
    do_add_sq(&msg);

    return 0;
}

//查询回调
int callback(void *arg, int f_num, char **f_value, char **f_name){
    
    msg_t msg = *(msg_t *)arg;
    strcpy(msg.user.ID, f_value[0]);
    strcpy(msg.user.password, f_value[1]);
    msg.deviceonline = atoi(f_value[2]);

    msg.switchset.swlight = atoi(f_value[3]);
    msg.switchset.swfan = atoi(f_value[4]);
    msg.switchset.swmotor = atoi(f_value[5]);

    msg.limitset.tempup = atoi(f_value[6]);
    msg.limitset.tempdown = atoi(f_value[7]);
    msg.limitset.humeup = atoi(f_value[8]);
    msg.limitset.humedown = atoi(f_value[9]);
    msg.limitset.luxup = atoi(f_value[10]);
    msg.limitset.luxdown = atoi(f_value[11]);

    *(msg_t *)arg = msg;
    printf("查询到的名字：%s, 光强：%d\n", msg.user.ID, msg.limitset.luxdown);

    return 0;
}

int do_login_sq(msg_t *loginfo)
{
    if (-1 == do_user_exists(loginfo)){
        return -1;
    }
    
    char sq_buff[N] = {0};
    sprintf(sq_buff, "select * from user where ID='%s'",
            loginfo->user.ID);

    if (SQLITE_OK != sqlite3_exec(db, sq_buff, callback, (void *)loginfo, NULL)){
        printf("查询失败\n");
        return -1;
    }
    return 0;
}
//查询返回信息
void do_query_info(msg_t *msg){

    char sq_buff[N] = {0};
    sprintf(sq_buff, "select * from user where ID='%s'",
            msg->user.ID);

    if (SQLITE_OK != sqlite3_exec(db, sq_buff, callback, (void *)msg, NULL)){
        printf("查询失败\n");
    }
}
// 添加数据库
void do_add_sq(msg_t *msg)
{

    char sq_buff[N] = {0};
    sprintf(sq_buff, "insert into user values('%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d)", 
                                            msg->user.ID, msg->user.password, msg->deviceonline, 
                                            msg->switchset.swlight, msg->switchset.swfan, msg->switchset.swmotor, 
                                            msg->limitset.tempup, msg->limitset.tempdown, 
                                            msg->limitset.humeup, msg->limitset.humedown, 
                                            msg->limitset.luxup, msg->limitset.luxdown);

    int ret = 0;
    if (SQLITE_OK != (ret = sqlite3_exec(db, sq_buff, NULL, NULL, NULL))){
        if (ret == 19){
            //printf("此用户已存在\n");
        }else{
            //printf("添加失败\n");
        }
        return;
    }
}
// 判断一个用户是否已经存在
int do_user_exists(msg_t *msg)
{
    char sq_buff[N] = {0};
    sprintf(sq_buff, "select * from user where ID='%s' and password='%s'",
            msg->user.ID, msg->user.password);
    int nrow = 0, ncolumn = 0;
    char **result = NULL;
    if (SQLITE_OK != sqlite3_get_table(db, sq_buff, &result, &nrow, &ncolumn, NULL)){
        printf("查询失败\n");
        return -1;
    }
    sqlite3_free_table(result);
    if (nrow == 0){
        return -1;
    }
    return 0;
}
//下位机登录后更改状态
void do_change_online_sq(msg_t *msg){

    char sq_buff[N] = {0};
    sprintf(sq_buff, "update user set deviceonline=%d where ID='%s'", msg->deviceonline, msg->user.ID);
    if (SQLITE_OK != sqlite3_exec(db, sq_buff, NULL, NULL, NULL)){
        printf("在线状态修改失败\n");
    }else{
        printf("在线状态修改成功\n");
    }
}

//修改开关状态
void do_change_switch_sq(msg_t *msg){
    char sq_buff[N] = {0};
    sprintf(sq_buff, "update user set swlight=%d, swfan=%d, swmotor=%d where ID='%s'", 
            msg->switchset.swlight, msg->switchset.swfan, msg->switchset.swmotor, msg->user.ID);
    if (SQLITE_OK != sqlite3_exec(db, sq_buff, NULL, NULL, NULL)){
        printf("开关状态修改失败\n");
    }
}
//更改阈值
void do_change_threshold_sq(msg_t *msg){
    char sq_buff[N] = {0};
    sprintf(sq_buff, "update user set tempup=%d, tempdown=%d, humeup=%d, humedown=%d, luxup=%d, luxdown=%d where ID='%s'", 
            msg->limitset.tempup, msg->limitset.tempdown, 
            msg->limitset.humeup, msg->limitset.humedown, 
            msg->limitset.luxup, msg->limitset.luxdown, msg->user.ID);
    if (SQLITE_OK != sqlite3_exec(db, sq_buff, NULL, NULL, NULL)){
        printf("阈值状态修改失败\n");
    }
}
//关闭数据库
void do_close_sq()
{
    sqlite3_close(db);
}
